package com.cn.jdia.attence.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.google.gson.Gson;

public class DBManager4PG {

	// public static final String DRIVER = "org.postgresql.Driver";
	// public static final String USER = "postgres";
	// public static final String PASS = "Qd83789957";
	// public static final String URL =
	// "jdbc:postgresql://172.20.5.172:5432/cmsdb";

	// 每天0点刷新一次
	public static String initializeData() {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		DateTime datetime = new DateTime();
		String nowDate = datetime.toString("YYYYMMdd");
		// System.out.println(nowDate);
		String yesterday = datetime.minusDays(1).toString("YYYYMMdd");

		try {
			// Class.forName(DRIVER).newInstance();
			// Connection conn = DriverManager.getConnection(URL, USER, PASS);

			ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
			DataSource ds = (DataSource) w_context.getBean("dataSource");
			Connection conn = ds.getConnection();

			Statement st = conn.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select person_name,check_date,person_phone,person_dept,check_time,is_valid,person_id,turnover,is_valid");
			sb.append(" from jan_attence where check_date='").append(nowDate).append("'");
			// sb.append("'").append(" or
			// check_date='").append(yesterday).append("'");
			// System.out.println(sb.toString());
			// sb.append(" and is_valid=true");

			ResultSet rs = st.executeQuery(sb.toString());
			while (rs.next()) {

				Map<String, String> map = new HashMap<String, String>();
				map.put("name", rs.getString("person_name"));
				map.put("dept", rs.getString("person_dept"));
				map.put("phone", rs.getString("person_phone"));
				map.put("date", rs.getString("check_date"));
				map.put("pid", rs.getString("person_id"));
				map.put("valid", rs.getString("is_valid"));
				
				// if (yesterday.equals(rs.getString("check_date"))) {
				if (rs.getInt("turnover") == 0) {
					map.put("today", "false");
				} else if (rs.getInt("turnover") == 1) {
					map.put("today", "true");
				}

				list.add(map);
			}

			rs.close();
			st.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		Gson gson = new Gson();
		String result = gson.toJson(list);
		return result;
	}

	// refresh per 5 second
	public static String getDate() {
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		DateTime datetime = new DateTime();
		String nowDate = datetime.toString("YYYYMMdd");

		String timestap = datetime.minusSeconds(10).toString("YYYY-MM-dd HH:mm:ss");

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource");

		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();
			StringBuffer sb = new StringBuffer();

			String nowtime = datetime.toString("hhmm");
			// System.out.println("nowtime:" + nowtime);
//			if ((nowtime.compareTo("0810") > 0) && (nowtime.compareTo("0840") < 0)) {
//				StringBuffer sb2 = new StringBuffer("select count(*) count from jan_attence where check_date='")
//						.append(nowDate).append("'");
//				ResultSet rs2 = st.executeQuery(sb2.toString());
//				int count = 0;
//				while (rs2.next()) {
//					count = rs2.getInt("count");
//				}
//				// System.out.println("count::" + count);
//
//				if (count == 40) {
//					Map<String, String> map = new HashMap<String, String>();
//					map.put("dept", "full");
//					list.add(map);
//				}
//				rs2.close();
//			}

			sb.append("select person_name,check_date,person_phone,person_dept,check_time,check_id,person_id,turnover,is_valid");
			sb.append(" from jan_attence where check_date='").append(nowDate).append("'");
			//sb.append(" and is_valid=false");  //modify 20170902

			// System.out.println(sb.toString());
			ResultSet rs = st.executeQuery(sb.toString());
			int count = 0;
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("name", rs.getString("person_name"));
				map.put("dept", rs.getString("person_dept"));
				map.put("phone", rs.getString("person_phone"));
				map.put("id", rs.getString("person_id"));
				map.put("checktime", rs.getString("check_time"));
				map.put("check_id", rs.getString("check_id"));
				map.put("pid", rs.getString("person_id"));
				map.put("valid", rs.getString("is_valid"));

				if (rs.getInt("turnover") == 1) {
					map.put("today", "true");
				} else if (rs.getInt("turnover") == 0) {
					map.put("today", "false");
				}

				list.add(map);
				count++;
			}
			rs.close();

			//取完一次数后将所有is_valid置为true
			String updateall="UPDATE jan_attence set is_valid=TRUE where check_date='"+nowDate+"'";
			st.executeUpdate(updateall);			
			
			if (count == 0) {// 无新记录
				st.close();
				conn.close();
				// return "[]";
				Gson gson = new Gson();
				String result = gson.toJson(list);
				return result;

			} else {// 新查出的记录入库,或更新
				Iterator<Map<String, String>> it = list.iterator();
				while (it.hasNext()) {
					Map<String, String> map = it.next();
					String check_id = map.get("check_id");

					// annotate @ 2017-12-2
					String upSql = "update jan_attence set is_valid = true where check_id='" + check_id + "'";
					st.execute(upSql);
				}
				st.close();
				conn.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		Gson gson = new Gson();
		String result = gson.toJson(list);
		return result;
	}

	// get 5 second date.
	// now it has been never used.
	// public static String getDate_bak() {
	// List<Map<String, String>> list = new ArrayList<Map<String, String>>();
	// DateTime datetime = new DateTime();
	// String nowDate = datetime.toString("YYYYMMdd");
	//
	// String timestap = datetime.minusSeconds(10).toString("YYYY-MM-dd
	// HH:mm:ss");
	// // String timestap = datetime.minusSeconds(70).toString("YYYY-MM-dd
	// // HH:mm:ss");
	// // System.out.println(timestap);
	//
	// try {
	// // Class.forName(DRIVER).newInstance();
	// // Connection conn = DriverManager.getConnection(URL, USER, PASS);
	//
	// ClassPathXmlApplicationContext w_context = new
	// ClassPathXmlApplicationContext("springmvc.xml");
	// DataSource ds = (DataSource) w_context.getBean("dataSource");
	// Connection conn = ds.getConnection();
	//
	// Statement st = conn.createStatement();
	// StringBuffer sb = new StringBuffer();
	// sb.append(
	// "select a.person_name,a.dept_name,a.event_card,a.event_time,b.phone from
	// ac_event a,oc_person b ");
	// sb.append(" where a.event_card=b.identity_id ");
	// sb.append(" and a.event_time>'").append(timestap).append("'");
	// // sb.append(" order by event_time desc limit 1");
	//
	// // System.out.println(sb.toString());
	//
	// ResultSet rs = st.executeQuery(sb.toString());
	// int count = 0;
	// while (rs.next()) {
	// Map<String, String> map = new HashMap<String, String>();
	// map.put("name", rs.getString("person_name"));
	// map.put("dept", rs.getString("dept_name"));
	// map.put("phone", rs.getString("phone"));
	// map.put("id", rs.getString("event_card"));
	// map.put("checktime", rs.getString("event_time"));
	// list.add(map);
	// count++;
	// }
	// rs.close();
	//
	// // System.out.println("count:"+count);
	// if (count == 0) {// 无新记录
	// st.close();
	// conn.close();
	// return "[]";
	// } else {// 新查出的记录入库,或更新
	// Iterator<Map<String, String>> it = list.iterator();
	// while (it.hasNext()) {
	// Map<String, String> map = it.next();
	//
	// StringBuffer sql = new StringBuffer();
	// sql.append("select * from jan_attence where
	// check_date='").append(nowDate).append("'");
	// sql.append(" and person_dept='").append(map.get("dept")).append("'");
	//
	// ResultSet rs2 = st.executeQuery(sql.toString());
	// String check_id = "";
	// while (rs2.next()) {
	// check_id = rs2.getString("check_id"); // primary key
	// }
	// rs2.close();
	//
	// String update = "";
	// if (!"".equals(check_id)) {// not null ,update
	// update = "update jan_attence set person_name='" + map.get("name") + "',
	// person_phone='"
	// + map.get("phone") + "',check_time='" +
	// map.get("checktime").substring(11)
	// + "',person_id='" + map.get("id") + "' where check_id='" + check_id +
	// "'";
	// // System.out.println(update);
	// st.execute(update);
	// // boolean res = st.execute(update);
	// // System.out.println("update:" + res);
	// } else {// null, insert with new check_id
	// String idsql = "select max(check_id) max from jan_attence";
	// ResultSet rs3 = st.executeQuery(idsql);
	// String id = "";
	// while (rs3.next()) {
	// id = rs3.getString("max");
	// }
	// System.out.println("id:" + id);
	// int pkid = Integer.parseInt(id);
	// pkid++;
	// rs3.close();
	//
	// update = "insert into jan_attence values('" + map.get("name") + "','" +
	// nowDate + "','"
	// + map.get("phone") + "','" + map.get("dept") + "','"
	// + map.get("checktime").substring(11) + "',true,'" + pkid + "','" +
	// map.get("id") + "')";
	// System.out.println(update);
	// boolean res = st.execute(update);
	// System.out.println("insert:" + res);
	// }
	//
	// }
	// }
	//
	// st.close();
	// conn.close();
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	// Gson gson = new Gson();
	// String result = gson.toJson(list);
	// return result;
	// }

	public static void main(String[] args) {
		String data = initializeData();
		System.out.println(data);
	}
}
